clear all
set more off, perm
set maxvar 120000
set varabbrev off
* ---------------------------------------------- *
global dir 	"`1'"
global Data 	$dir/Data
global Tables 	$dir/Tables
global Figures 	$dir/Figures
global Work 	$dir/Work
global Temp 	$dir/Temp
global Pseudo	$dir/Work/Temp
* ---------------------------------------------- *
foreach EBR in qnic {
foreach PORTF in BOOK INV SIZE PROF MOME {
use using $Work/Temp/portfolio_`PORTF'_`EBR', clear
drop cumRet*

foreach var of varlist cum*Mo1 {
	replace `var'=12*`var'
}	
foreach var of varlist cum*Qr1 {
	replace `var'=4*`var'
}	
foreach var of varlist cum*Yr3 {
	replace `var'=`var'/3
}	
foreach var of varlist cum*Yr5 {
	replace `var'=`var'/5
}	
matrix actu = J(5,2,.)
matrix sprd = J(5,2,.)
matrix corr = J(5,2,.)

forvalues p=1(4)5  {
local i=1
foreach var in Mo1 Qr1 Yr1 Yr3 Yr5  {
	local w=(`p'==1)+2*(`p'==5)
	quietly: regress cumQRet`var' if portf==`p'
	matrix sprd[`i',`w']=_b[_cons]

	quietly: regress cumNRet`var' if portf==`p'
	matrix actu[`i',`w']=_b[_cons]

	pwcorr cumNRet`var' cumQRet`var' if portf==`p'
	local rho: display %04.2f r(rho) 
	display `rho'
	matrix corr[`i',`w']=`rho'
	local i=`i'+1
	}
}
matrix list sprd
matrix list actu
matrix list corr

putexcel set $Tables/Table01_02_June.xlsx, sheet(`PORTF') modify
putexcel A2="1Mo"
putexcel A3="3Mo"
putexcel A4="1Yr"
putexcel A5="3Yr"
putexcel A6="5Yr"

putexcel A9 ="1Mo"
putexcel A10="3Mo"
putexcel A11="1Yr"
putexcel A12="3Yr"
putexcel A13="5Yr"

putexcel A16="1Mo"
putexcel A17="3Mo"
putexcel A18="1Yr"
putexcel A19="3Yr"
putexcel A20="5Yr"

putexcel B1="LOW  `PORTF' Actual"
putexcel C1="HIGH `PORTF' Actual"

putexcel B8="LOW  `PORTF' EBRD"
putexcel C8="High `PORTF' EBRD"

putexcel B15="LOW  `PORTF' Corr"
putexcel C15="HIGH `PORTF' Corr"

putexcel B2=matrix(actu) , nformat(percent_d2)
putexcel B9=matrix(sprd) , nformat(percent_d2)
putexcel B16=matrix(corr), nformat(percent)
}
}
